package model.db

import model.dto.MessageSqlDTO
import model.dto.ProcessApplyDto
import model.enums.ThemeType
import model.repository.UserRepository
import model.vo.*
import java.io.File
import java.sql.ResultSet
import java.sql.SQLException
import java.util.function.Consumer


/**
 * @author wei kunJie
 * @date 2024/4/18 13:36:24
 */
object Db {
    // db 文件存放路径地址
    val dbPath = "db" + File.separator
    var sqliteHelper: SqliteHelper? = null;

    fun initDb() {
        // 1、创建sqlite连接
        val dbFilePath: String = dbPath + "sqlite-chat-db.db"
        // 需要判断文件是否存在，不存在则优先创建 .db 文件
        val dbFile = File(dbFilePath)
        // 如果父路径不存在，则先创建父路径
        if (!dbFile.parentFile.exists()) {
            dbFile.parentFile.mkdirs()
        }
        // 如果文件不存在，则创建文件
        if (!dbFile.exists()) {
            dbFile.createNewFile()
        }
        // 建立连接
        sqliteHelper = SqliteHelper(dbFilePath)
    }

    private fun createTable() {
        // 2、建表 DDL
        val createUser =
            "create table if not exists user(" +
                    "id integer primary key autoincrement," +
                    "username varchar(20)," +
                    "password varchar(20)" +
                    ")"
        // 执行sql
        sqliteHelper!!.executeUpdate(createUser)
    }

    /**
     * 保存好友通知
     */
    fun saveFriendNotice(friendNotificationItem: FriendNotificationItem) {
        val friendNoticeById = friendNotificationItem.id?.let { getFriendNoticeById(it) }
        if (friendNoticeById == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_friend_notice(id,head_image,user_name,time,content,apply_state,notice_type,login_user_account,friend_account) " +
                    "values ('${friendNotificationItem.id}'," +
                    "'${friendNotificationItem.headImage}'," +
                    "'${friendNotificationItem.userName}'," +
                    "'${friendNotificationItem.time}'," +
                    "'${friendNotificationItem.content}'," +
                    "'${friendNotificationItem.applyState}'," +
                    "'${friendNotificationItem.noticeType}'," +
                    "'${UserRepository.user?.account}'," +
                    "'${friendNotificationItem.friendAccount}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateUserData = "update t_friend_notice " +
                    "set head_image = '${friendNotificationItem.headImage}', " +
                    "user_name = '${friendNotificationItem.userName}', " +
                    "time = '${friendNotificationItem.time}', " +
                    "content = '${friendNotificationItem.content}', " +
                    "apply_state = '${friendNotificationItem.applyState}', " +
                    "notice_type = '${friendNotificationItem.noticeType}', " +
                    "friend_account = '${friendNotificationItem.friendAccount}' " +
                    "where id = '${friendNotificationItem.id}' and login_user_account = '${UserRepository.user?.account}'"
            sqliteHelper!!.executeUpdate(updateUserData)
        }
    }

    fun updateFriendNotice(processApplyDto: ProcessApplyDto) {
        val updateStatement = "update t_friend_notice " +
                "set " +
                "apply_state = '${processApplyDto.state}' " +
                "where id = '${processApplyDto.applyId}' and login_user_account = '${UserRepository.user?.account}'"

        sqliteHelper?.executeUpdate(updateStatement)

    }

    fun getFriendNoticeById(applyId: Long): FriendNotificationItem? {
        val selectUserData =
            "select * from t_friend_notice where id = '$applyId' and login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["head_image"] = rs.getString("head_image")
                resultMap["user_name"] = rs.getString("user_name")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["apply_state"] = rs.getString("apply_state")
                resultMap["notice_type"] = rs.getString("notice_type")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["friend_account"] = rs.getString("friend_account")
                resultMap["notice_type"] = rs.getString("notice_type")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        query.forEach { resultMap ->
            return FriendNotificationItem(
                id = resultMap["id"].toString().toLong(),
                headImage = resultMap["head_image"].toString(),
                userName = resultMap["user_name"].toString(),
                time = resultMap["time"].toString(),
                content = resultMap["content"].toString(),
                applyState = resultMap["apply_state"].toString(),
                friendAccount = resultMap["friend_account"].toString(),
                noticeType = resultMap["notice_type"].toString()
            )
        }
        return null
    }

    fun getFriendNotices(): List<FriendNotificationItem>? {
        val selectUserData =
            "select * from t_friend_notice where login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["head_image"] = rs.getString("head_image")
                resultMap["user_name"] = rs.getString("user_name")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["apply_state"] = rs.getString("apply_state")
                resultMap["notice_type"] = rs.getString("notice_type")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["friend_account"] = rs.getString("friend_account")
                resultMap["notice_type"] = rs.getString("notice_type")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<FriendNotificationItem>()
        query.forEach { resultMap ->
            val friendNotificationItem = FriendNotificationItem(
                id = resultMap["id"].toString().toLong(),
                headImage = resultMap["head_image"].toString(),
                userName = resultMap["user_name"].toString(),
                time = resultMap["time"].toString(),
                content = resultMap["content"].toString(),
                applyState = resultMap["apply_state"].toString(),
                friendAccount = resultMap["friend_account"].toString(),
                noticeType = resultMap["notice_type"].toString()
            )
            list.add(friendNotificationItem)
        }
        return list;
    }

    fun saveGroupNotice(groupNotificationItem: GroupNotificationItem) {
        val friendNoticeById = groupNotificationItem.id?.let { getGroupNoticeById(it) }
        if (friendNoticeById == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_group_notice(id,head_image,group_name,time,content,apply_state,notice_type,login_user_account,group_account) " +
                    "values ('${groupNotificationItem.id}'," +
                    "'${groupNotificationItem.headImage}'," +
                    "'${groupNotificationItem.groupName}'," +
                    "'${groupNotificationItem.time}'," +
                    "'${groupNotificationItem.content}'," +
                    "'${groupNotificationItem.applyState}'," +
                    "'${groupNotificationItem.noticeType}'," +
                    "'${UserRepository.user?.account}'," +
                    "'${groupNotificationItem.groupAccount}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateUserData = "update t_friend_notice " +
                    "set head_image = '${groupNotificationItem.headImage}', " +
                    "group_name = '${groupNotificationItem.groupName}', " +
                    "time = '${groupNotificationItem.time}', " +
                    "content = '${groupNotificationItem.content}', " +
                    "apply_state = '${groupNotificationItem.applyState}', " +
                    "notice_type = '${groupNotificationItem.noticeType}', " +
                    "group_account = '${groupNotificationItem.groupAccount}' " +
                    "where id = '${groupNotificationItem.id}' and login_user_account = '${UserRepository.user?.account}'"
            sqliteHelper!!.executeUpdate(updateUserData)
        }
    }

    fun updateGroupNotice(processApplyDto: ProcessApplyDto) {
        val updateStatement = "update t_group_notice " +
                "set " +
                "apply_state = '${processApplyDto.state}' " +
                "where id = '${processApplyDto.applyId}' and login_user_account = '${UserRepository.user?.account}'"

        sqliteHelper?.executeUpdate(updateStatement)

    }

    fun getGroupNoticeById(applyId: Long): GroupNotificationItem? {
        val selectUserData =
            "select * from t_group_notice where id = '$applyId' and login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["head_image"] = rs.getString("head_image")
                resultMap["group_name"] = rs.getString("group_name")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["apply_state"] = rs.getString("apply_state")
                resultMap["notice_type"] = rs.getString("notice_type")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["group_account"] = rs.getString("group_account")
                resultMap["notice_type"] = rs.getString("notice_type")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        query.forEach { resultMap ->
            return GroupNotificationItem(
                id = resultMap["id"].toString().toLong(),
                headImage = resultMap["head_image"].toString(),
                groupName = resultMap["group_name"].toString(),
                time = resultMap["time"].toString(),
                content = resultMap["content"].toString(),
                applyState = resultMap["apply_state"].toString(),
                groupAccount = resultMap["group_account"].toString(),
                noticeType = resultMap["notice_type"].toString()
            )
        }
        return null
    }

    fun getGroupNotices(): List<GroupNotificationItem>? {
        val selectUserData =
            "select * from t_group_notice where login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["head_image"] = rs.getString("head_image")
                resultMap["group_name"] = rs.getString("group_name")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["apply_state"] = rs.getString("apply_state")
                resultMap["notice_type"] = rs.getString("notice_type")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["group_account"] = rs.getString("group_account")
                resultMap["notice_type"] = rs.getString("notice_type")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<GroupNotificationItem>()
        query.forEach { resultMap ->
            val groupNotificationItem = GroupNotificationItem(
                id = resultMap["id"].toString().toLong(),
                headImage = resultMap["head_image"].toString(),
                groupName = resultMap["group_name"].toString(),
                time = resultMap["time"].toString(),
                content = resultMap["content"].toString(),
                applyState = resultMap["apply_state"].toString(),
                groupAccount = resultMap["group_account"].toString(),
                noticeType = resultMap["notice_type"].toString()
            )
            list.add(groupNotificationItem)
        }
        return list
    }

    /**
     * 保存消息
     */
    fun saveMessage(messageSqlDTO: MessageSqlDTO) {
        if (getMessageById(messageSqlDTO.id!!) == null) {
            //插入数据
            val insertMessageData = "INSERT INTO t_message " +
                    "(id, time, content, user_account, user_name, user_avatar, to_user_account, is_group, login_user_account) VALUES " +
                    "('${messageSqlDTO.id}', '${messageSqlDTO.time}', '${messageSqlDTO.content}', '${messageSqlDTO.userAccount}', " +
                    "'${messageSqlDTO.userName}', '${messageSqlDTO.userAvatar}', '${messageSqlDTO.toUserAccount}', " +
                    "'${messageSqlDTO.isGroup}', '${messageSqlDTO.loginUserAccount}')"
            sqliteHelper!!.executeUpdate(insertMessageData)
        } else {
            val updateMessageData = "UPDATE t_message SET " +
                    "time = '${messageSqlDTO.time}', " +
                    "content = '${messageSqlDTO.content}', " +
                    "user_account = '${messageSqlDTO.userAccount}', " +
                    "user_name = '${messageSqlDTO.userName}', " +
                    "user_avatar = '${messageSqlDTO.userAvatar}', " +
                    "to_user_account = '${messageSqlDTO.toUserAccount}', " +
                    "is_group = '${messageSqlDTO.isGroup}' " +
                    "WHERE id = '${messageSqlDTO.id}' AND login_user_account = '${messageSqlDTO.loginUserAccount}'"
            sqliteHelper!!.executeUpdate(updateMessageData)
        }
    }

    /**
     * 获取消息记录
     */
    fun getMessages(): List<MessageSqlDTO>? {
        val selectMessageData = "SELECT * FROM t_message WHERE login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectMessageData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["user_account"] = rs.getString("user_account")
                resultMap["user_name"] = rs.getString("user_name")
                resultMap["user_avatar"] = rs.getString("user_avatar")
                resultMap["to_user_account"] = rs.getString("to_user_account")
                resultMap["is_group"] = rs.getString("is_group")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                return resultMap
            }
        }) ?: return null
        val list = mutableListOf<MessageSqlDTO>()
        query.forEach {
            val messageSqlDTO = MessageSqlDTO()
            messageSqlDTO.id = it["id"] as? String
            messageSqlDTO.time = it["time"] as? String
            messageSqlDTO.content = it["content"] as? String
            messageSqlDTO.userAccount = it["user_account"] as? String
            messageSqlDTO.userName = it["user_name"] as? String
            messageSqlDTO.userAvatar = it["user_avatar"] as? String
            messageSqlDTO.toUserAccount = it["to_user_account"] as? String
            messageSqlDTO.isGroup = (it["is_group"] as? String) == "true"
            list.add(messageSqlDTO)
        }
        return list
    }

    fun getMessageById(messageId: String): MessageSqlDTO? {
        val selectMessageData =
            "SELECT * FROM t_message WHERE id = '${messageId}' AND login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectMessageData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["time"] = rs.getString("time")
                resultMap["content"] = rs.getString("content")
                resultMap["user_account"] = rs.getString("user_account")
                resultMap["user_name"] = rs.getString("user_name")
                resultMap["user_avatar"] = rs.getString("user_avatar")
                resultMap["to_user_account"] = rs.getString("to_user_account")
                resultMap["is_group"] = rs.getString("is_group")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                return resultMap
            }
        }) ?: return null
        query.forEach {
            val messageSqlDTO = MessageSqlDTO()
            messageSqlDTO.id = it["id"] as? String
            messageSqlDTO.time = it["time"] as? String
            messageSqlDTO.content = it["content"] as? String
            messageSqlDTO.userAccount = it["user_account"] as? String
            messageSqlDTO.userName = it["user_name"] as? String
            messageSqlDTO.userAvatar = it["user_avatar"] as? String
            messageSqlDTO.toUserAccount = it["to_user_account"] as? String
            messageSqlDTO.isGroup = (it["is_group"] as? String) == "true"
            return messageSqlDTO
        }
        return null

    }

    /**
     * 保存消息列表item
     */
    fun saveMsgList(messageListItem: MessageListItem) {
        if (getMsgListByAccount(messageListItem.account) == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_channel_mapping_item(account,time,is_group,login_user_account,name,content,avatar,unread_count) " +
                    "values ('${messageListItem.account}','${messageListItem.time}','${messageListItem.isGroup}'" +
                    ",'${UserRepository.user?.account}','${messageListItem.title}','${messageListItem.msg}','${messageListItem.headImage}'" +
                    ",'${messageListItem.count}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateUserData = "UPDATE t_channel_mapping_item SET " +
                    "time = '${messageListItem.time}', " +
                    "is_group = '${messageListItem.isGroup}', " +
                    "name = '${messageListItem.title}', " +
                    "content = '${messageListItem.msg}', " +
                    "avatar = '${messageListItem.headImage}', " +
                    "unread_count = '${messageListItem.count}' " +
                    "WHERE account = '${messageListItem.account}' and login_user_account = '${UserRepository.user?.account}'"
            sqliteHelper!!.executeUpdate(updateUserData)
        }
    }

    fun getMsgListByAccount(account: String): MessageListItem? {
        val selectUserData =
            "select * from t_channel_mapping_item where account = '$account' and login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["account"] = rs!!.getString("account")
                resultMap["time"] = rs.getString("time")
                resultMap["is_group"] = rs.getString("is_group")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["name"] = rs.getString("name")
                resultMap["content"] = rs.getString("content")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["unread_count"] = rs.getString("unread_count")
                return resultMap
            }
        })
        if (query.isNullOrEmpty()) return null
        val messageListItem = MessageListItem()
        query.forEach { resultMap ->
            messageListItem.account = resultMap["account"] as String
            messageListItem.time = resultMap["time"] as String
            messageListItem.isGroup = (resultMap["is_group"] as String) == "true"
            messageListItem.title = resultMap["name"] as String
            messageListItem.msg = resultMap["content"] as String
            messageListItem.headImage = resultMap["avatar"] as String
            messageListItem.count = (resultMap["unread_count"] as String).toInt()
        }
        return messageListItem
    }

    fun getMsgLists(): List<MessageListItem>? {
        val selectUserData =
            "select * from t_channel_mapping_item where login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["account"] = rs!!.getString("account")
                resultMap["time"] = rs.getString("time")
                resultMap["is_group"] = rs.getString("is_group")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                resultMap["name"] = rs.getString("name")
                resultMap["content"] = rs.getString("content")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["unread_count"] = rs.getString("unread_count")
                return resultMap
            }
        })
        if (query.isNullOrEmpty()) return null
        val list = mutableListOf<MessageListItem>()
        query.forEach { resultMap ->
            val messageListItem = MessageListItem()
            messageListItem.account = resultMap["account"] as String
            messageListItem.time = resultMap["time"] as String
            messageListItem.isGroup = (resultMap["is_group"] as String) == "true"
            messageListItem.title = resultMap["name"] as String
            messageListItem.msg = resultMap["content"] as String
            messageListItem.headImage = resultMap["avatar"] as String
            messageListItem.count = (resultMap["unread_count"] as String).toInt()
            list.add(messageListItem)
        }
        return list
    }

    fun saveLastLoginAccount(account: String) {
        val lastLoginAccount = getLastLoginAccount()
        if (lastLoginAccount == "") {
            //插入数据
            val insertUserData = "insert into " +
                    "t_login_account(id,account) " +
                    "values (0,'$account'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateSQL = "UPDATE t_login_account " +
                    "SET account = '$account'" +
                    "WHERE id = 0"
            sqliteHelper!!.executeUpdate(updateSQL)
        }
    }

    fun getLastLoginAccount(): String {
        val selectUserData = "select * from t_login_account"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["account"] = rs!!.getString("account")
                return resultMap
            }
        })
        return if (query.isNullOrEmpty())
            ""
        else query[0]["account"] as String
    }

    /**
     * 保存登陆成功的用户
     */
    fun saveUser(userVo: UserVo, psw: String) {
        val user = userVo.account?.let { getUser(it) }
        if (user == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_user(id,account,username,password,avatar,email,phone,sex,theme,age) " +
                    "values ('${userVo.account}'," +
                    "'${userVo.account}'," +
                    "'${userVo.username}'," +
                    "'${psw}'," +
                    "'${userVo.avatar}'," +
                    "'${userVo.email}'," +
                    "'${userVo.phone}'," +
                    "'${userVo.sex}'," +
                    "'${UserRepository.theme}'," +
                    "${userVo.age}" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            if (!user.theme.isNullOrEmpty()) {
                if (user.theme == "Blue") {
                    UserRepository.theme = ThemeType.Blue
                } else {
                    UserRepository.theme = ThemeType.Pink
                }
            }
            val updateSQL = "UPDATE t_user " +
                    "SET " +
                    "    username = '${userVo.username}'," +
                    "    password = '${psw}'," +
                    "    avatar = '${userVo.avatar}'," +
                    "    email = '${userVo.email}'," +
                    "    phone = '${userVo.phone}'," +
                    "    sex = '${userVo.sex}'," +
                    "    age = ${userVo.age} " +
                    "WHERE" +
                    "    account = '${userVo.account}';"
            sqliteHelper!!.executeUpdate(updateSQL)
        }

    }

    fun updateThemeType(theme: ThemeType) {
        UserRepository.theme = theme
        val updateSQL = "UPDATE t_user " +
                "SET " +
                "    theme = '${theme}' " +
                "WHERE" +
                "    account = '${UserRepository.user?.account}';"
        sqliteHelper!!.executeUpdate(updateSQL)
    }

    /**
     * 根据账号查找用户
     */
    fun getUser(account: String): UserSQLVo? {
        val selectUserData = "select * from t_user where account = '$account'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["account"] = rs.getString("account")
                resultMap["username"] = rs.getString("username")
                resultMap["password"] = rs.getString("password")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["email"] = rs.getString("email")
                resultMap["phone"] = rs.getString("phone")
                resultMap["sex"] = rs.getString("sex")
                resultMap["age"] = rs.getString("age")
                resultMap["theme"] = rs.getString("theme")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val userVo = UserSQLVo()
        query.forEach(Consumer {
            userVo.id = it["id"] as String
            userVo.account = it["account"] as String
            userVo.username = it["username"] as String
            userVo.password = it["password"] as String
            userVo.avatar = it["avatar"] as String
            userVo.email = it["email"] as String
            userVo.phone = it["phone"] as String
            userVo.sex = it["sex"] as String
            userVo.theme = it["theme"] as String
            userVo.age = (it["age"] as String).toInt()
        })
        return userVo
    }

    fun getAllUser(): List<UserSQLVo>? {
        val selectUserData = "select * from t_user"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["id"] = rs!!.getString("id")
                resultMap["account"] = rs.getString("account")
                resultMap["username"] = rs.getString("username")
                resultMap["password"] = rs.getString("password")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["email"] = rs.getString("email")
                resultMap["phone"] = rs.getString("phone")
                resultMap["sex"] = rs.getString("sex")
                resultMap["age"] = rs.getString("age")
                resultMap["theme"] = rs.getString("theme")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<UserSQLVo>()

        query.forEach(Consumer {
            val userVo = UserSQLVo()
            userVo.id = it["id"] as String
            userVo.account = it["account"] as String
            userVo.username = it["username"] as String
            userVo.password = it["password"] as String
            userVo.avatar = it["avatar"] as String
            userVo.email = it["email"] as String
            userVo.phone = it["phone"] as String
            userVo.sex = it["sex"] as String
            userVo.theme = it["theme"] as String
            userVo.age = (it["age"] as String).toInt()
            list.add(userVo)
        })
        return list
    }

    fun saveFriend(friendsMappingItem: FriendsMappingItem) {
        val friend = getFriendByAccount(friendsMappingItem.account)
        if (friend == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_friend(id,account,username,avatar,email,phone,sex,age,login_user_account) " +
                    "values ('${friendsMappingItem.account}'," +
                    "'${friendsMappingItem.account}'," +
                    "'${friendsMappingItem.userName}'," +
                    "'${friendsMappingItem.headUrl}'," +
                    "'${friendsMappingItem.userVo?.email}'," +
                    "'${friendsMappingItem.userVo?.phone}'," +
                    "'${friendsMappingItem.userVo?.sex}'," +
                    "${friendsMappingItem.userVo?.age}," +
                    "'${UserRepository.user?.account}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateStatement = "update t_friend " +
                    "set username = '${friendsMappingItem.userName}', " +
                    "avatar = '${friendsMappingItem.headUrl}', " +
                    "email = '${friendsMappingItem.userVo?.email}', " +
                    "phone = '${friendsMappingItem.userVo?.phone}', " +
                    "sex = '${friendsMappingItem.userVo?.sex}', " +
                    "age = ${friendsMappingItem.userVo?.age} " +
                    "where account = '${friendsMappingItem.account}' and login_user_account = '${UserRepository.user?.account}'"
            sqliteHelper?.executeUpdate(updateStatement)

        }
    }

    fun getFriendByAccount(account: String): FriendsMappingItem? {
        val selectUserData =
            "select * from t_friend where account = '$account' and login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["account"] = rs!!.getString("account")
                resultMap["username"] = rs.getString("username")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["email"] = rs.getString("email")
                resultMap["phone"] = rs.getString("phone")
                resultMap["sex"] = rs.getString("sex")
                resultMap["age"] = rs.getString("age")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        query.forEach { resultMap ->
            val userVo = UserVo()
            userVo.account = resultMap["account"] as String
            userVo.username = resultMap["username"] as String
            userVo.avatar = resultMap["avatar"] as String
            userVo.email = resultMap["email"] as String
            userVo.phone = resultMap["phone"] as String
            userVo.sex = resultMap["sex"] as String
            userVo.age = (resultMap["age"] as String).toInt()
            return FriendsMappingItem(
                account = resultMap["account"].toString(),
                userName = resultMap["username"].toString(),
                headUrl = resultMap["avatar"].toString(),
                state = "[未知]",
                personalSignature = "暂无修改签名",
                userVo = userVo
            )
        }
        return null

    }

    fun getFriends(): List<FriendsMappingItem>? {
        val selectUserData = "select * from t_friend where login_user_account = ${UserRepository.user?.account}"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["account"] = rs!!.getString("account")
                resultMap["username"] = rs.getString("username")
                resultMap["avatar"] = rs.getString("avatar")
                resultMap["email"] = rs.getString("email")
                resultMap["phone"] = rs.getString("phone")
                resultMap["sex"] = rs.getString("sex")
                resultMap["age"] = rs.getString("age")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<FriendsMappingItem>()
        query.forEach { resultMap ->
            val userVo = UserVo()
            userVo.account = resultMap["account"] as String
            userVo.username = resultMap["username"] as String
            userVo.avatar = resultMap["avatar"] as String
            userVo.email = resultMap["email"] as String
            userVo.phone = resultMap["phone"] as String
            userVo.sex = resultMap["sex"] as String
            userVo.age = (resultMap["age"] as String).toInt()
            list.add(
                FriendsMappingItem(
                    account = resultMap["account"].toString(),
                    userName = resultMap["username"].toString(),
                    headUrl = resultMap["avatar"].toString(),
                    state = "[未知]",
                    personalSignature = "暂无修改签名",
                    userVo = userVo
                )
            )
        }
        return list
    }

    fun saveGroup(groupMappingItem: GroupMappingItem) {
        val group = getGroupByGroupAccount(groupMappingItem.groupAccount)
        if (group == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_group(groupAccount,groupAvatar,groupName,login_user_account) " +
                    "values ('${groupMappingItem.groupAccount}'," +
                    "'${groupMappingItem.groupHeadUrl}'," +
                    "'${groupMappingItem.groupName}'," +
                    "'${UserRepository.user?.account}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        } else {
            val updateStatement = "update t_group " +
                    "set groupAvatar = '${groupMappingItem.groupHeadUrl}', " +
                    "groupName = '${groupMappingItem.groupName}' " +
                    "where groupAccount = '${group.groupAccount}' and login_user_account = '${UserRepository.user?.account}'"
            sqliteHelper?.executeUpdate(updateStatement)
        }
    }

    fun getGroups(): List<GroupMappingItem>? {
        val selectUserData =
            "select * from t_group where login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["groupAccount"] = rs!!.getString("groupAccount")
                resultMap["groupAvatar"] = rs.getString("groupAvatar")
                resultMap["groupName"] = rs.getString("groupName")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<GroupMappingItem>()
        query.forEach { resultMap ->
            val groupMappingItem = GroupMappingItem(
                groupAccount = resultMap["groupAccount"] as String,
                groupHeadUrl = resultMap["groupAvatar"] as String,
                groupName = resultMap["groupName"] as String
            )
            list.add(groupMappingItem)
        }
        return list
    }

    fun getGroupByGroupAccount(groupAccount: String): GroupMappingItem? {
        val selectUserData =
            "select * from t_group where groupAccount = '$groupAccount' and login_user_account = '${UserRepository.user?.account}'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["groupAccount"] = rs!!.getString("groupAccount")
                resultMap["groupName"] = rs.getString("groupName")
                resultMap["groupAvatar"] = rs.getString("groupAvatar")
                resultMap["login_user_account"] = rs.getString("login_user_account")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        query.forEach { resultMap ->
            return GroupMappingItem(
                groupAccount = resultMap["groupAccount"] as String,
                groupName = resultMap["groupName"] as String,
                groupHeadUrl = resultMap["groupAvatar"] as String,
            )
        }
        return null
    }

    fun saveGroupUser(groupAccount: String, userVo: UserVo) {
        val groupUsers = getGroupUsers(groupAccount)
        if (groupUsers == null || groupUsers.find { it.account == userVo.account } == null) {
            //插入数据
            val insertUserData = "insert into " +
                    "t_group_user(groupAccount,userAccount,userAvatar,userName) " +
                    "values ('$groupAccount'," +
                    "'${userVo.account}'," +
                    "'${userVo.avatar}'," +
                    "'${userVo.username}'" +
                    ")"
            sqliteHelper!!.executeUpdate(insertUserData)
        }
    }

    fun getGroupUsers(groupAccount: String): List<UserVo>? {
        val selectUserData =
            "select * from t_group_user where groupAccount = '$groupAccount'"
        val query = sqliteHelper?.executeQuery(selectUserData, object : RowMapper<MutableMap<String, Any>> {
            @Throws(SQLException::class)
            override fun mapRow(rs: ResultSet?, index: Int): MutableMap<String, Any> {
                val resultMap: MutableMap<String, Any> = HashMap()
                resultMap["groupAccount"] = rs!!.getString("groupAccount")
                resultMap["userAccount"] = rs.getString("userAccount")
                resultMap["userAvatar"] = rs.getString("userAvatar")
                resultMap["userName"] = rs.getString("userName")
                return resultMap
            }
        })
        if (query.isNullOrEmpty())
            return null
        val list = mutableListOf<UserVo>()
        query.forEach { resultMap ->
            val userVo = UserVo()
            userVo.account = resultMap["userAccount"] as String
            userVo.username = resultMap["userName"] as String
            userVo.avatar = resultMap["userAvatar"] as String
            list.add(userVo)
        }
        return list

    }

}